<?php

/**
 * This is the model class for table "mst_client".
 *
 * The followings are the available columns in table 'mst_client':
 * @property integer $id
 * @property string $client_number
 * @property string $name
 * @property string $address
 * @property integer $city_id
 * @property integer $province_id
 * @property string $zip
 * @property string $phone
 * @property string $mobile
 * @property string $fax
 * @property integer $createdby
 * @property string $createdtime
 * @property integer $updatedby
 * @property string $updatedtime
 * @property integer $parent_id
 * @property string $is_corporate
 * @property string $is_pakk
 */
class MstClient extends CActiveRecord
{
public $policy_no;
public $jml_installment;
public $nota_debet_no;
public $id_client;
public $start_periode;
public $end_periode;
public $sum_insured;
public $jenis_asuransi;
public $mst_client_id;
public $file;
	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'mst_client';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('name, address', 'required'),
			array('city_id, province_id, createdby, updatedby, parent_id, groups', 'numerical', 'integerOnly'=>true),
			array('client_number, name', 'length', 'max'=>50),
			array('address', 'length', 'max'=>100),
			array('zip', 'length', 'max'=>10),
			array('phone, mobile, fax', 'length', 'max'=>25),
			array('is_corporate, is_pakk', 'length', 'max'=>1),
			// The following rule is used by search().
			// @todo Please remove those attributes that should not be searched.
			array('id, client_number, name, address, city_id, province_id, zip, phone, mobile, fax, createdby, createdtime, updatedby, updatedtime, parent_id, is_corporate, is_pakk, groups', 'safe', 'on'=>'search'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'id' => 'ID',
			'client_number' => 'Client Number',
			'name' => 'Name',
			'address' => 'Address',
			'city_id' => 'City',
			'province_id' => 'Province',
			'zip' => 'Zip',
			'phone' => 'Phone',
			'mobile' => 'Mobile',
			'fax' => 'Fax',
			'createdby' => 'Createdby',
			'createdtime' => 'Createdtime',
			'updatedby' => 'Updatedby',
			'updatedtime' => 'Updatedtime',
			'parent_id' => 'Parent',
			'is_corporate' => 'Is Corporate',
			'is_pakk' => 'Is Pakk',
		);
	}

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 *
	 * Typical usecase:
	 * - Initialize the model fields with values from filter form.
	 * - Execute this method to get CActiveDataProvider instance which will filter
	 * models according to data in model fields.
	 * - Pass data provider to CGridView, CListView or any similar widget.
	 *
	 * @return CActiveDataProvider the data provider that can return the models
	 * based on the search/filter conditions.
	 */
	public function search()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;
		$criteria->condition='parent_id=0';

		$criteria->compare('id',$this->id);
		$criteria->compare('client_number',$this->client_number,true);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('address',$this->address,true);
		$criteria->compare('city_id',$this->city_id);
		$criteria->compare('province_id',$this->province_id);
		$criteria->compare('zip',$this->zip,true);
		$criteria->compare('phone',$this->phone,true);
		$criteria->compare('mobile',$this->mobile,true);
		$criteria->compare('fax',$this->fax,true);
		$criteria->compare('createdby',$this->createdby);
		$criteria->compare('createdtime',$this->createdtime,true);
		$criteria->compare('updatedby',$this->updatedby);
		$criteria->compare('updatedtime',$this->updatedtime,true);
		$criteria->compare('parent_id',$this->parent_id);
		$criteria->compare('is_corporate',$this->is_corporate,true);
		$criteria->compare('is_pakk',$this->is_pakk,true);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	
	public function search2($par)
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;
		$criteria->condition = 'is_corporate=\'0\' and parent_id='.$par;

		$criteria->compare('id',$this->id);
		$criteria->compare('client_number',$this->client_number,true);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('address',$this->address,true);
		$criteria->compare('city_id',$this->city_id);
		$criteria->compare('province_id',$this->province_id);
		$criteria->compare('zip',$this->zip,true);
		$criteria->compare('phone',$this->phone,true);
		$criteria->compare('mobile',$this->mobile,true);
		$criteria->compare('fax',$this->fax,true);
		$criteria->compare('createdby',$this->createdby);
		$criteria->compare('createdtime',$this->createdtime,true);
		$criteria->compare('updatedby',$this->updatedby);
		$criteria->compare('updatedtime',$this->updatedtime,true);
		$criteria->compare('parent_id',$this->parent_id);
		$criteria->compare('is_corporate',$this->is_corporate,true);
		$criteria->compare('is_pakk',$this->is_pakk,true);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	
	
	public function search3()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;
		//$criteria->condition = 'parent_id=0';

		$criteria->compare('id',$this->id);
		$criteria->compare('client_number',$this->client_number,true);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('address',$this->address,true);
		$criteria->compare('city_id',$this->city_id);
		$criteria->compare('province_id',$this->province_id);
		$criteria->compare('zip',$this->zip,true);
		$criteria->compare('phone',$this->phone,true);
		$criteria->compare('mobile',$this->mobile,true);
		$criteria->compare('fax',$this->fax,true);
		$criteria->compare('createdby',$this->createdby);
		$criteria->compare('createdtime',$this->createdtime,true);
		$criteria->compare('updatedby',$this->updatedby);
		$criteria->compare('updatedtime',$this->updatedtime,true);
		$criteria->compare('parent_id',$this->parent_id);
		$criteria->compare('is_corporate',$this->is_corporate,true);
		$criteria->compare('is_pakk',$this->is_pakk,true);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	
	public function searchModal()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;

		$criteria->select='a.id,a.jml_installment, a.file, a.mst_client_id, t.client_number,t.name,t.address,t.city_id, a.policy_no, a.start_periode, a.end_periode';
		$criteria->join='join reg_health_policy a on t.id=a.mst_client_id';
		$criteria->compare('client_number',$this->client_number,true);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('address',$this->address,true);
		$criteria->compare('city_id',$this->city_id);
		

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}


	public function searchModal2()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;

		$criteria->select='c.name as jenis_asuransi,a.id, a.mst_client_id, a.jml_installment,t.client_number,t.name,t.address,t.city_id, a.policy_no, a.start_periode, a.end_periode, a.sum_insured';
		$criteria->join =' join reg_health_policy_non a on t.id=a.mst_client_id  ';
		$criteria->join .= ' join mst_jns_asuransi c on a.mst_jns_asuransi_id = c.id ';
		$criteria->compare('client_number',$this->client_number,true);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('address',$this->address,true);
		$criteria->compare('city_id',$this->city_id);
		

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	
	public function searchAllclient()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;

		$criteria->select='t.id,t.name,t.client_number';
		$criteria->compare('client_number',$this->client_number,true);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('address',$this->address,true);
		$criteria->compare('city_id',$this->city_id);
		

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	
	public function searchAllnota()
	{
		$sql = " select 
a.id as id_client,c.nota_debet_no,d.*,c.nominal
from mst_client a 
inner join reg_health_policy b on a.id = b.mst_client_id
inner join ndk_health c on b.id = c.reg_health_policy_id
inner join (
	select a.*,b.nominal as nilai_tukar,b.code
	from (
		select distinct max(id) id_nominal,currency_id
		from mst_currency_detail group by currency_id
	)a inner join mst_currency_detail b on a.currency_id = b.id
) d on b.mst_currency_id = d.currency_id
where c.is_bayar ='0'



";
/*
		$sql="select 
			a.id as id_client,c.nota_debet_no
			from mst_client a 
			inner join reg_health_policy b on a.id = b.mst_client_id
			inner join ndk_health c on b.id = c.reg_health_policy_id
			where c.is_bayar ='0'
			union
			select 
			a.id as id_client,c.nota_debet_no
			from mst_client a 
			inner join reg_health_policy_non b on a.id = b.mst_client_id
			inner join ndk_health_non c on b.id = c.reg_nonhealth_policy_id
			where c.is_bayar ='0'";
*/
			$dataProvider=new CSqlDataProvider($sql, array(
				'totalItemCount'=>$count,
				'sort'=>array(
					'attributes'=>array(
						 'agent_id', //and all other atributes with withc you want to sort
					),
				),
				'pagination'=>array(
					'pageSize'=>10,
				),
			));
			return $dataProvider;
	}
	/**
	 * Returns the static model of the specified AR class.
	 * Please note that you should have this exact method in all your CActiveRecord descendants!
	 * @param string $className active record class name.
	 * @return MstClient the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}
}
